package com.cencat.common.utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.function.Function;
import java.util.function.Supplier;

/**
 * 数据库工具类
 * 提供数据库连接管理、事务处理、SQL执行等功能
 * 
 * @author cencat
 * @since 2024-01-01
 */
public class DatabaseUtils {

    private static final Logger log = LoggerFactory.getLogger(DatabaseUtils.class);

    /**
     * 私有构造函数，防止实例化
     */
    private DatabaseUtils() {
    }

    /**
     * 数据源
     */
    private static DataSource dataSource;

    /**
     * JDBC模板
     */
    private static JdbcTemplate jdbcTemplate;

    /**
     * 事务管理器
     */
    private static DataSourceTransactionManager transactionManager;

    /**
     * 设置数据源
     * 
     * @param ds 数据源
     */
    public static void setDataSource(DataSource ds) {
        dataSource = ds;
        if (ds != null) {
            jdbcTemplate = new JdbcTemplate(ds);
            transactionManager = new DataSourceTransactionManager(ds);
        }
    }

    /**
     * 获取数据库连接
     * 
     * @return 数据库连接
     * @throws SQLException SQL异常
     */
    public static Connection getConnection() throws SQLException {
        if (dataSource == null) {
            throw new SQLException("数据源未初始化");
        }
        return dataSource.getConnection();
    }

    /**
     * 关闭数据库连接
     * 
     * @param connection 数据库连接
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("关闭数据库连接失败: {}", e.getMessage(), e);
            }
        }
    }

    /**
     * 关闭Statement
     * 
     * @param statement Statement对象
     */
    public static void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("关闭Statement失败: {}", e.getMessage(), e);
            }
        }
    }

    /**
     * 关闭ResultSet
     * 
     * @param resultSet ResultSet对象
     */
    public static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error("关闭ResultSet失败: {}", e.getMessage(), e);
            }
        }
    }

    /**
     * 关闭数据库资源
     * 
     * @param connection 数据库连接
     * @param statement Statement对象
     * @param resultSet ResultSet对象
     */
    public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) {
        closeResultSet(resultSet);
        closeStatement(statement);
        closeConnection(connection);
    }

    // ==================== 事务管理 ====================

    /**
     * 开始事务
     * 
     * @return 事务状态
     */
    public static TransactionStatus beginTransaction() {
        return beginTransaction(TransactionDefinition.ISOLATION_DEFAULT);
    }

    /**
     * 开始事务
     * 
     * @param isolationLevel 隔离级别
     * @return 事务状态
     */
    public static TransactionStatus beginTransaction(int isolationLevel) {
        if (transactionManager == null) {
            throw new RuntimeException("事务管理器未初始化");
        }

        DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
        definition.setIsolationLevel(isolationLevel);
        definition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        
        return transactionManager.getTransaction(definition);
    }

    /**
     * 提交事务
     * 
     * @param status 事务状态
     */
    public static void commitTransaction(TransactionStatus status) {
        if (transactionManager != null && status != null) {
            try {
                transactionManager.commit(status);
            } catch (Exception e) {
                log.error("提交事务失败: {}", e.getMessage(), e);
                throw e;
            }
        }
    }

    /**
     * 回滚事务
     * 
     * @param status 事务状态
     */
    public static void rollbackTransaction(TransactionStatus status) {
        if (transactionManager != null && status != null) {
            try {
                transactionManager.rollback(status);
            } catch (Exception e) {
                log.error("回滚事务失败: {}", e.getMessage(), e);
            }
        }
    }

    /**
     * 在事务中执行
     * 
     * @param supplier 执行逻辑
     * @param <T> 返回类型
     * @return 执行结果
     */
    public static <T> T executeInTransaction(Supplier<T> supplier) {
        return executeInTransaction(supplier, TransactionDefinition.ISOLATION_DEFAULT);
    }

    /**
     * 在事务中执行
     * 
     * @param supplier 执行逻辑
     * @param isolationLevel 隔离级别
     * @param <T> 返回类型
     * @return 执行结果
     */
    public static <T> T executeInTransaction(Supplier<T> supplier, int isolationLevel) {
        if (supplier == null) {
            return null;
        }

        TransactionStatus status = null;
        try {
            status = beginTransaction(isolationLevel);
            T result = supplier.get();
            commitTransaction(status);
            return result;
        } catch (Exception e) {
            rollbackTransaction(status);
            log.error("事务执行失败: {}", e.getMessage(), e);
            throw new RuntimeException("事务执行失败", e);
        }
    }

    /**
     * 在事务中执行（无返回值）
     * 
     * @param runnable 执行逻辑
     */
    public static void executeInTransaction(Runnable runnable) {
        executeInTransaction(runnable, TransactionDefinition.ISOLATION_DEFAULT);
    }

    /**
     * 在事务中执行（无返回值）
     * 
     * @param runnable 执行逻辑
     * @param isolationLevel 隔离级别
     */
    public static void executeInTransaction(Runnable runnable, int isolationLevel) {
        if (runnable == null) {
            return;
        }

        executeInTransaction(() -> {
            runnable.run();
            return null;
        }, isolationLevel);
    }

    // ==================== SQL执行 ====================

    /**
     * 执行查询SQL
     * 
     * @param sql SQL语句
     * @param params 参数
     * @return 查询结果
     */
    public static List<Map<String, Object>> queryForList(String sql, Object... params) {
        if (jdbcTemplate == null) {
            throw new RuntimeException("JdbcTemplate未初始化");
        }

        try {
            if (params != null && params.length > 0) {
                return jdbcTemplate.queryForList(sql, params);
            } else {
                return jdbcTemplate.queryForList(sql);
            }
        } catch (Exception e) {
            log.error("执行查询SQL失败: sql={}, params={}, error={}", sql, Arrays.toString(params), e.getMessage(), e);
            throw new RuntimeException("执行查询SQL失败", e);
        }
    }

    /**
     * 执行查询SQL（单条记录）
     * 
     * @param sql SQL语句
     * @param params 参数
     * @return 查询结果
     */
    public static Map<String, Object> queryForMap(String sql, Object... params) {
        List<Map<String, Object>> list = queryForList(sql, params);
        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * 执行查询SQL（指定类型）
     * 
     * @param sql SQL语句
     * @param rowMapper 行映射器
     * @param params 参数
     * @param <T> 返回类型
     * @return 查询结果
     */
    public static <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... params) {
        if (jdbcTemplate == null) {
            throw new RuntimeException("JdbcTemplate未初始化");
        }

        try {
            if (params != null && params.length > 0) {
                return jdbcTemplate.query(sql, rowMapper, params);
            } else {
                return jdbcTemplate.query(sql, rowMapper);
            }
        } catch (Exception e) {
            log.error("执行查询SQL失败: sql={}, params={}, error={}", sql, Arrays.toString(params), e.getMessage(), e);
            throw new RuntimeException("执行查询SQL失败", e);
        }
    }

    /**
     * 执行查询SQL（单个对象）
     * 
     * @param sql SQL语句
     * @param rowMapper 行映射器
     * @param params 参数
     * @param <T> 返回类型
     * @return 查询结果
     */
    public static <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... params) {
        List<T> list = query(sql, rowMapper, params);
        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * 执行查询SQL（单个值）
     * 
     * @param sql SQL语句
     * @param clazz 值类型
     * @param params 参数
     * @param <T> 返回类型
     * @return 查询结果
     */
    public static <T> T queryForObject(String sql, Class<T> clazz, Object... params) {
        if (jdbcTemplate == null) {
            throw new RuntimeException("JdbcTemplate未初始化");
        }

        try {
            if (params != null && params.length > 0) {
                return jdbcTemplate.queryForObject(sql, clazz, params);
            } else {
                return jdbcTemplate.queryForObject(sql, clazz);
            }
        } catch (Exception e) {
            log.error("执行查询SQL失败: sql={}, params={}, error={}", sql, Arrays.toString(params), e.getMessage(), e);
            return null;
        }
    }

    /**
     * 执行更新SQL
     * 
     * @param sql SQL语句
     * @param params 参数
     * @return 影响行数
     */
    public static int update(String sql, Object... params) {
        if (jdbcTemplate == null) {
            throw new RuntimeException("JdbcTemplate未初始化");
        }

        try {
            if (params != null && params.length > 0) {
                return jdbcTemplate.update(sql, params);
            } else {
                return jdbcTemplate.update(sql);
            }
        } catch (Exception e) {
            log.error("执行更新SQL失败: sql={}, params={}, error={}", sql, Arrays.toString(params), e.getMessage(), e);
            throw new RuntimeException("执行更新SQL失败", e);
        }
    }

    /**
     * 批量执行更新SQL
     * 
     * @param sql SQL语句
     * @param batchArgs 批量参数
     * @return 影响行数数组
     */
    public static int[] batchUpdate(String sql, List<Object[]> batchArgs) {
        if (jdbcTemplate == null) {
            throw new RuntimeException("JdbcTemplate未初始化");
        }

        try {
            return jdbcTemplate.batchUpdate(sql, batchArgs);
        } catch (Exception e) {
            log.error("批量执行更新SQL失败: sql={}, batchSize={}, error={}", sql, batchArgs.size(), e.getMessage(), e);
            throw new RuntimeException("批量执行更新SQL失败", e);
        }
    }

    // ==================== 数据库信息 ====================

    /**
     * 获取数据库元信息
     * 
     * @return 数据库元信息
     */
    public static DatabaseMetaData getDatabaseMetaData() {
        Connection connection = null;
        try {
            connection = getConnection();
            return connection.getMetaData();
        } catch (SQLException e) {
            log.error("获取数据库元信息失败: {}", e.getMessage(), e);
            return null;
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * 获取数据库产品名称
     * 
     * @return 数据库产品名称
     */
    public static String getDatabaseProductName() {
        try {
            DatabaseMetaData metaData = getDatabaseMetaData();
            return metaData != null ? metaData.getDatabaseProductName() : null;
        } catch (SQLException e) {
            log.error("获取数据库产品名称失败: {}", e.getMessage(), e);
            return null;
        }
    }

    /**
     * 获取数据库版本
     * 
     * @return 数据库版本
     */
    public static String getDatabaseProductVersion() {
        try {
            DatabaseMetaData metaData = getDatabaseMetaData();
            return metaData != null ? metaData.getDatabaseProductVersion() : null;
        } catch (SQLException e) {
            log.error("获取数据库版本失败: {}", e.getMessage(), e);
            return null;
        }
    }

    /**
     * 检查表是否存在
     * 
     * @param tableName 表名
     * @return 是否存在
     */
    public static boolean tableExists(String tableName) {
        if (CencatStringUtils.isBlank(tableName)) {
            return false;
        }

        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            resultSet = metaData.getTables(null, null, tableName.toUpperCase(), new String[]{"TABLE"});
            return resultSet.next();
        } catch (SQLException e) {
            log.error("检查表是否存在失败: tableName={}, error={}", tableName, e.getMessage(), e);
            return false;
        } finally {
            closeResultSet(resultSet);
            closeConnection(connection);
        }
    }

    /**
     * 获取表的列信息
     * 
     * @param tableName 表名
     * @return 列信息列表
     */
    public static List<ColumnInfo> getTableColumns(String tableName) {
        if (CencatStringUtils.isBlank(tableName)) {
            return Collections.emptyList();
        }

        List<ColumnInfo> columns = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            resultSet = metaData.getColumns(null, null, tableName.toUpperCase(), null);
            
            while (resultSet.next()) {
                ColumnInfo column = new ColumnInfo();
                column.setColumnName(resultSet.getString("COLUMN_NAME"));
                column.setDataType(resultSet.getInt("DATA_TYPE"));
                column.setTypeName(resultSet.getString("TYPE_NAME"));
                column.setColumnSize(resultSet.getInt("COLUMN_SIZE"));
                column.setNullable(resultSet.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                column.setDefaultValue(resultSet.getString("COLUMN_DEF"));
                column.setRemarks(resultSet.getString("REMARKS"));
                columns.add(column);
            }
        } catch (SQLException e) {
            log.error("获取表列信息失败: tableName={}, error={}", tableName, e.getMessage(), e);
        } finally {
            closeResultSet(resultSet);
            closeConnection(connection);
        }
        
        return columns;
    }

    /**
     * 执行SQL脚本文件
     * 
     * @param scriptPath 脚本文件路径
     * @return 是否执行成功
     */
    public static boolean executeSqlScript(String scriptPath) {
        if (CencatStringUtils.isBlank(scriptPath)) {
            return false;
        }

        Connection connection = null;
        Statement statement = null;
        try {
            String scriptContent = FileUtils.readFileToString(scriptPath);
            if (CencatStringUtils.isBlank(scriptContent)) {
                return false;
            }

            connection = getConnection();
            statement = connection.createStatement();
            
            // 分割SQL语句（以分号分隔）
            String[] sqlStatements = scriptContent.split(";");
            for (String sql : sqlStatements) {
                sql = sql.trim();
                if (CencatStringUtils.isNotBlank(sql)) {
                    statement.execute(sql);
                }
            }
            
            return true;
        } catch (Exception e) {
            log.error("执行SQL脚本失败: scriptPath={}, error={}", scriptPath, e.getMessage(), e);
            return false;
        } finally {
            closeStatement(statement);
            closeConnection(connection);
        }
    }

    /**
     * 测试数据库连接
     * 
     * @return 是否连接成功
     */
    public static boolean testConnection() {
        Connection connection = null;
        try {
            connection = getConnection();
            return connection != null && !connection.isClosed();
        } catch (SQLException e) {
            log.error("测试数据库连接失败: {}", e.getMessage(), e);
            return false;
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * 获取当前时间（数据库时间）
     * 
     * @return 当前时间
     */
    public static java.util.Date getCurrentTime() {
        try {
            return queryForObject("SELECT NOW()", java.util.Date.class);
        } catch (Exception e) {
            log.error("获取数据库当前时间失败: {}", e.getMessage(), e);
            return new java.util.Date();
        }
    }

    /**
     * 列信息
     */
    public static class ColumnInfo {
        private String columnName;
        private int dataType;
        private String typeName;
        private int columnSize;
        private boolean nullable;
        private String defaultValue;
        private String remarks;

        // Getters and Setters
        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public int getDataType() {
            return dataType;
        }

        public void setDataType(int dataType) {
            this.dataType = dataType;
        }

        public String getTypeName() {
            return typeName;
        }

        public void setTypeName(String typeName) {
            this.typeName = typeName;
        }

        public int getColumnSize() {
            return columnSize;
        }

        public void setColumnSize(int columnSize) {
            this.columnSize = columnSize;
        }

        public boolean isNullable() {
            return nullable;
        }

        public void setNullable(boolean nullable) {
            this.nullable = nullable;
        }

        public String getDefaultValue() {
            return defaultValue;
        }

        public void setDefaultValue(String defaultValue) {
            this.defaultValue = defaultValue;
        }

        public String getRemarks() {
            return remarks;
        }

        public void setRemarks(String remarks) {
            this.remarks = remarks;
        }

        @Override
        public String toString() {
            return "ColumnInfo{" +
                    "columnName='" + columnName + '\'' +
                    ", dataType=" + dataType +
                    ", typeName='" + typeName + '\'' +
                    ", columnSize=" + columnSize +
                    ", nullable=" + nullable +
                    ", defaultValue='" + defaultValue + '\'' +
                    ", remarks='" + remarks + '\'' +
                    '}';
        }
    }
}